﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using System.Data.OleDb;
namespace DAO
{
    public class PhongDAO : Abstract
    {
        public static bool ThemPhong(PhongDTO phong)
        {
            string query = "insert into Phong (TenPhong, MaLoaiPhong, TrangThai,Dong,Cot) values ('{0}',{1},{2},{3},{4})"
                .Replace("{0}", phong.TenPhong)
                .Replace("{1}", phong.MaLoaiPhong.ToString())
                .Replace("{2}", phong.TrangThai.ToString())
                .Replace("{3}",phong.Dong.ToString())
                .Replace("{4}",phong.Cot.ToString());

            return Query(query);
        }
        public static bool SuaPhong(PhongDTO phong)
        {
            string query = "update Phong set  MaLoaiPhong = {0}, TrangThai = {1}, TenPhong = '{3}' where MaPhong = {2}"
                .Replace("{0}", phong.MaLoaiPhong.ToString())
                .Replace("{1}", phong.TrangThai.ToString())
                .Replace("{2}", phong.MaPhong.ToString())
                .Replace("{3}",phong.TenPhong);
            return Query(query);
        }
        public static bool SuaViTriPhong(PhongDTO phong)
        {
            string query = "update Phong set Dong = {0}, Cot = {1} where TenPhong = '{2}'"
                .Replace("{0}",phong.Dong.ToString())
                .Replace("{1}",phong.Cot.ToString())
                .Replace("{2}",phong.TenPhong);
            return Query(query);
        }
        public static bool KiemTraViTriPhongDaCoPhong(int dong, int cot)//kiem tra xem vi tri nay da co phong chua
        {
            bool kq;
            OleDbConnection conn = null;
            try
            {
                conn = GetConnection();
                conn.Open();
            }
            catch
            {
                throw new Exception("Connect error!");
            }
            try
            {
                string query = "select count(*) from Phong where Dong = {0} and Cot = {1} "
                    .Replace("{0}",dong.ToString())
                    .Replace("{1}",cot.ToString());
                
                int count = (int)(new OleDbCommand(query, conn).ExecuteScalar());
                if (count > 0)
                    kq = true;
                else
                    kq = false;
            }
            catch (Exception e)
            {
                throw;
            }

            if (conn.State == System.Data.ConnectionState.Open)
                conn.Close();
            return kq;
        }
        public static bool XoaPhong(int _maPhong)
        {
            string query = "delete from Phong where MaPhong = {0}"
                .Replace("{0}", _maPhong.ToString());
            return Query(query);
        }
        public static List<PhongDTO> LayDSPhong(int maPhong = -1, string tenPhong = null, int trangThai = -1, int maLoaiPhong = -1)//LayDSPhong(null,1)
        {
            OleDbConnection conn = null;
            List<PhongDTO> listPhong = new List<PhongDTO>();
            try
            {
                conn = GetConnection();
                conn.Open();
            }
            catch
            {
                throw new Exception("Connect error!");
            }
            try
            {
                string query = "select MaPhong,TenPhong, MaLoaiPhong, TrangThai,Dong,Cot from Phong ";
                if (maPhong != -1)
                    query += "where MaPhong = {2}".Replace("{2}", maPhong.ToString()) ;
                if (tenPhong != null)//lay ds phong theo ma phong
                    query = query + "where TenPhong = '{0}'".Replace("{0}", tenPhong);
                if( maLoaiPhong != -1)
                    query = query + "where MaLoaiPhong = {3}".Replace("{3}", maLoaiPhong.ToString());
                else if (trangThai != -1)//lay ds phong theo trang thai
                    query = query + "where TrangThai = {1}".Replace("{1}", trangThai.ToString());
                //else query giu nguyen => lay het ds phong
                OleDbDataReader rd = new OleDbCommand(query, conn).ExecuteReader();
                while (rd.Read())
                {
                    PhongDTO phong = new PhongDTO();

                    phong.MaPhong = rd.GetInt32(0);
                    phong.TenPhong = rd.GetString(1);
                    phong.MaLoaiPhong = rd.GetInt32(2);
                    phong.TrangThai = rd.GetInt32(3);
                    phong.Dong = rd.GetInt32(4);
                    phong.Cot = rd.GetInt32(5);
                    listPhong.Add(phong);
                }
            }
            catch (Exception e)
            {
                listPhong = null;
            }

            if (conn.State == System.Data.ConnectionState.Open)
                conn.Close();
            return listPhong;
        }
    }
}
